package com.jdbc.dbutils.test;

import com.jdbc.entity.Dog;
import com.jdbc.entity.DogVo;
import com.jdbc.entity.Types;
import com.jdbc.utils.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wzm
 * @create 2023-09-18 10:21
 * project_name:javaweb2308
 */
public class DBUtilsCRUD {

    /**
     * 新增dog信息的方法
     * @param dog 传入需要新增的dog对象信息
     * @return 返回执行新增以后受影响的行数
     */
    public int testInsert(Dog dog) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();//执行数据库操作的对象

        //连接
        Connection conn = JDBCUtils.getConn();
        //sql
        String sql = "insert into dog(name,age,sex) values(?,?,?)";


        int i =
                queryRunner.update(conn, sql, dog.getName(), dog.getAge(), dog.getSex());

        System.out.println(" 当前添加记录数为： " +i);
        return  i;
    }


    //修改
    public int testUpdate(Dog dog) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "update dog set name = ?,age = ?,sex = ? where id = ?";
        int i = queryRunner.update(conn, sql, dog.getName(), dog.getAge(), dog.getSex(), dog.getId());
        return i;
    }

    //删除
    public int testDetele(Dog dog) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "delete from dog where id = ?";
        int i = queryRunner.update(conn, sql, dog.getId());
        return i;
    }


    //查询单个
    public Dog findById(Integer id) throws SQLException {
        QueryRunner  queryRunner = new QueryRunner();
        //处理查询到的结果是一个bean对象
        BeanHandler<Dog> handler = new BeanHandler<>(Dog.class);
        Dog dog = queryRunner.query(JDBCUtils.getConn(), "select * from dog where id = ?", handler, id);
        return  dog;
    }

    //查询集合
    public List<Dog> findByAll() throws SQLException {
        QueryRunner  queryRunner = new QueryRunner();
        //查询处理集合
        BeanListHandler<Dog> handler = new BeanListHandler<>(Dog.class);
        List<Dog> dogList = queryRunner.query(JDBCUtils.getConn(), "select * from dog where id > ?", handler, 5);
        return dogList;
    }

    //查询单个值  聚合函数  max  min avg count sum

    public int testQueryVal() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        //查询单个值的handler
        ScalarHandler handler = new ScalarHandler<>();
        Object query = queryRunner.query(conn, "select count(1) from dog", handler);

        //转化数字  object == > 整数
        //先转为Number 再转为int
        Number query1 = (Number) query;
        int i = query1.intValue();
        DbUtils.closeQuietly(conn);
        return i;


    }




    @Test
    public void findByAll001() throws SQLException {
        QueryRunner  queryRunner = new QueryRunner();
        //自定义结果集处理
  /*      ResultSetHandler<List<DogVo>> handler = new ResultSetHandler<List<DogVo>>() {
            //实现匿名实现类中的方法  专门处理结果集的方法
            @Override
            public List<DogVo> handle(ResultSet resultSet) throws SQLException {
                //手动处理查询后的值
                List<DogVo> list = new ArrayList<>();
                DogVo dogVo = null;
                while (resultSet.next()){
                    dogVo = new DogVo();
                    //dog
                    dogVo.setId(resultSet.getInt("id"));
                    dogVo.setName(resultSet.getString("name"));
                    dogVo.setAge(resultSet.getInt("age"));
                    dogVo.setSex(resultSet.getString("sex"));

                    //type
                    int type_id = resultSet.getInt("type_id");
                    String type_name = resultSet.getString("type_name");
                    dogVo.setTypes(new Types(type_id,type_name));
                    list.add(dogVo);
                }
                System.out.println("list = " + list);
                return list;
            }
        };
*/

        DogHandler<List<DogVo>> handler = new DogHandler<>();

        Connection conn = JDBCUtils.getConn();
        String sql = "select d.id,\n" +
                "d.name,\n" +
                "d.age,\n" +
                "d.sex,\n" +
                "d.type_id,"+
                "t.type_name\n" +
                " from dog d \n" +
                "left join type t\n" +
                "on d.type_id = t.type_id";
        List<DogVo> query = queryRunner.query(conn, sql, handler);
        System.out.println("query = " + query);

    }




}


